/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.company.user.dao.impl;

import com.company.regis.db.BaseDao;
import com.company.regis.db.entity.ReportUser;
import com.company.report.dao.ReportUserDao;
import java.util.List;
import org.springframework.stereotype.Repository;

/**
 *
 * @author totoland
 */
@Repository
public class ReportUserDaoImpl extends BaseDao implements ReportUserDao {

    @Override
    public List<ReportUser> groupByProvince() {

        String SQL = "SELECT P.PROVINCE_ID ,P.PROVINCE_NAME, "
                + " "
                + "( "
                + "  SELECT COUNT(P.PROVINCE_ID) FROM REG_ADDRESS  "
                + "  INNER JOIN PROVINCE ON PROVINCE.PROVINCE_ID = REG_ADDRESS.PROVINCE_ID "
                + "  WHERE P.PROVINCE_ID = PROVINCE.PROVINCE_ID "
                + ") COUNTS "
                + " "
                + "FROM REG_ADDRESS R "
                + "INNER JOIN PROVINCE P ON P.PROVINCE_ID = R.PROVINCE_ID "
                + "GROUP BY P.PROVINCE_ID, P.PROVINCE_NAME "
                + " "
                + "";

        return findNativeQuery(SQL, ReportUser.class);
    }

}
